package com.schema2uml;

import java.math.BigDecimal;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.eclipse.emf.common.util.URI;
import org.eclipse.uml2.uml.AggregationKind;
import org.eclipse.uml2.uml.Association;
import org.eclipse.uml2.uml.Model;
import org.eclipse.uml2.uml.Package;
import org.eclipse.uml2.uml.Profile;
import org.eclipse.uml2.uml.Property;
import org.eclipse.uml2.uml.Stereotype;
import org.eclipse.uml2.uml.UMLFactory;

public class Schema2UMLTransformer {

	private final static Logger logger = Logger
			.getLogger(Schema2UMLTransformer.class);

	private String tableNamePattern = null;

	private String columnNamePattern = null;

	private Map<String, String> typeMappings = null;
	
	private Map<String, String> replacementRules = null;

	private String modelFile = null;

	private String modelName = null;

	private String packageName = null;
	
	private String profilefile = null;
	
	private String stereotypePersistentObject = null;
	
	private String stereotypePersistentColumn = null;
	
	private String stereotypePersistentAssociation = null;

	private DatabaseHelper databaseHelper = null;
	
	private UMLHelper umlHelper = null;

	private Map<String, org.eclipse.uml2.uml.Class> classes = new HashMap<String, org.eclipse.uml2.uml.Class>();

	private Map<String, Collection<String>> foreignKeys = new HashMap<String, Collection<String>>();

	private Model model = null;
	
	private Package leafPackage = null;

	private Profile profile = null;
	
	private Stereotype persistentObject = null;

	private Stereotype persistentProperty = null;
	
	private Stereotype persistentAssociation = null;
	
	public Schema2UMLTransformer(
			String jdbcDriver,
			String jdbcConnectionUrl,
			String jdbcUser, 
			String jdbcPassword,
			String schema,
			String tableNamePattern,
			String columnNamePattern,
			String mappings,
			String rules,
			String modelFile,
			String modelName,
			String packageName,
			String profileFile,
			String stereotypePersistentObject,
			String stereotypePersistentColumn,
			String stereotypePersistentAssociation,
			String umlResources
			) throws Exception {
		
		if(
				StringUtils.isEmpty(jdbcDriver)
				|| StringUtils.isEmpty(jdbcConnectionUrl)
				|| StringUtils.isEmpty(jdbcUser)
				|| StringUtils.isEmpty(jdbcPassword)
				|| StringUtils.isEmpty(schema)
				|| StringUtils.isEmpty(tableNamePattern)
				|| StringUtils.isEmpty(columnNamePattern)
				|| StringUtils.isEmpty(mappings)
				|| StringUtils.isEmpty(rules)
				|| StringUtils.isEmpty(modelFile)
				|| StringUtils.isEmpty(modelName)
				|| StringUtils.isEmpty(profileFile)
				|| StringUtils.isEmpty(stereotypePersistentObject)
				|| StringUtils.isEmpty(stereotypePersistentColumn)
				|| StringUtils.isEmpty(stereotypePersistentAssociation)
				|| StringUtils.isEmpty(umlResources)
				)
			throw new IllegalArgumentException();

		this.tableNamePattern = StringUtils.trim(tableNamePattern);
		this.columnNamePattern = StringUtils.trim(columnNamePattern);
		this.modelFile = modelFile;
		this.modelName = modelName;
		this.packageName = packageName;
		this.profilefile = profileFile;
		this.stereotypePersistentObject = stereotypePersistentObject;
		this.stereotypePersistentColumn = stereotypePersistentColumn;
		this.stereotypePersistentAssociation = stereotypePersistentAssociation;
		
		this.typeMappings = XMLHelper.getTypeMappings(mappings);
		this.replacementRules = XMLHelper.getClassNameReplacementRules(rules);
		
		this.databaseHelper = new DatabaseHelper(
				jdbcDriver, 
				jdbcConnectionUrl, 
				jdbcUser, 
				jdbcPassword, 
				schema);
		
		this.umlHelper = new UMLHelper(umlResources);
	}

	public void transform() throws Exception {
		createModel();
		applyProfile();
		initializeStereotypes();
		this.leafPackage = this.getOrCreatePackage(this.model, this.packageName);
		createClasses();
		this.databaseHelper.closeQuitely();
		this.umlHelper.save(model, URI.createURI(this.modelFile));
		logger.info("Created emf uml2 model with " + this.classes.size() + " classes.");
	}

	private void createModel() {
		this.model = UMLFactory.eINSTANCE.createModel();
		this.model.setName(modelName);
	}

	private void applyProfile() {
		this.profile = (Profile) this.umlHelper.load(URI.createURI(this.profilefile));
		this.profile.define();
		this.model.applyProfile(this.profile);
	}

	private void initializeStereotypes() {
		this.persistentObject = this.model.getAppliedProfile(this.profile.getName()).getOwnedStereotype(this.stereotypePersistentObject);
		this.persistentProperty = this.model.getAppliedProfile(this.profile.getName()).getOwnedStereotype(this.stereotypePersistentColumn);
		this.persistentAssociation = this.model.getAppliedProfile(this.profile.getName()).getOwnedStereotype(this.stereotypePersistentAssociation);
		if (persistentObject == null || persistentProperty == null || persistentAssociation == null) {
			throw new RuntimeException("Could not find stereotypes.");
		}
	}	

	protected Package getOrCreatePackage(Package modelPackage, String packageName) {
		packageName = StringUtils.trimToEmpty(packageName);
		if (StringUtils.isNotEmpty(packageName)) {
			String[] packages = packageName.split("\\.");
			if (packages != null && packages.length > 0) {
				for (int i = 0; i < packages.length; i++) {
					Package temp = modelPackage.getNestedPackage(packages[i]);
					if (temp == null) {
						temp = modelPackage.createNestedPackage(packages[i]);
					}
					modelPackage = temp;
				}
			}
		}
		return modelPackage;
	}

	protected void createClasses()
			throws SQLException {
		
		ResultSet resultSet = this.databaseHelper.getTables();

		while (resultSet.next()) {
			
			String tableName = resultSet.getString("TABLE_NAME");
			String tableCatalog = resultSet.getString("TABLE_CAT");
			String tableSchema = resultSet.getString("TABLE_SCHEM");
			
			if (tableName.matches(this.tableNamePattern)) {
				createClass(tableName, tableCatalog, tableSchema);
			} else {
				logger.warn("Table '" + tableName + "' does not match tableNamePattern.");
			}

			
		}
		DbUtils.closeQuietly(resultSet);
		if (this.classes.isEmpty()) {
			logger.warn("No classes created!");
		}

		Iterator<String> iter = this.classes.keySet().iterator();
		while (iter.hasNext()) {
			String tableName = iter.next();
			org.eclipse.uml2.uml.Class umlClass = (org.eclipse.uml2.uml.Class) classes.get(tableName);
			logger.info("Creating associations of class '" + umlClass.getName() + "'.");
			createAssociations(tableName);
			logger.info("Creating attributes of class '" + umlClass.getName() + "'.");
			createAttributes(umlClass, tableName);
		}
	}
	
	protected org.eclipse.uml2.uml.Class createClass(
			String tableName, 
			String tableCatalog,
			String tableSchema) {
		String className = StringHelper.className(tableName, replacementRules);
		logger.info("Creating class '" + className + "'.");
		org.eclipse.uml2.uml.Class umlClass = this.leafPackage.createOwnedClass(
				className, false);
		
		umlClass.applyStereotype(persistentObject);

		umlClass.setValue(persistentObject, "table", tableName);
		umlClass.setValue(persistentObject, "catalog", tableCatalog);
		umlClass.setValue(persistentObject, "schema", tableSchema);
		umlClass.setValue(persistentObject, "databaseType", "TABLE");
		// TODO description

		this.classes.put(tableName, umlClass);
		
		return umlClass;
	}



	protected void createAttributes(org.eclipse.uml2.uml.Class umlClass, String tableName)
			throws SQLException {
		
		final ResultSet columnRs = this.databaseHelper.getColumns(tableName);
		final Collection<String> primaryKeyColumns = this.databaseHelper.getPrimaryKeys(tableName);
		while (columnRs.next()) {
			final String columnName = columnRs.getString("COLUMN_NAME");
			if (columnName.matches(this.columnNamePattern)) {
				final String attributeName = StringHelper.attributeName(columnName);
				
				if (!this.hasForeignKey(tableName, columnName)) {
					logger.info("Adding attribute '" + attributeName + "'.");
					
					org.eclipse.uml2.uml.Class typeClass = null;

					String typeName = null; 
					String dbTypeName = StringHelper.typeName(columnRs.getString("TYPE_NAME"), columnRs.getString("COLUMN_SIZE"), columnRs.getString("DECIMAL_DIGITS"));
					String jdbcTypeName = this.databaseHelper.findJdbcType(columnRs.getInt("DATA_TYPE"));
					
					logger.info(" Searching in type mappings for db type '" + dbTypeName + "'.");
					if (typeMappings.containsKey(dbTypeName)) {
						typeName = dbTypeName;
					}
					if (typeName == null) {
						logger.info("  No result. Searching type mapping for jdbc type '" + jdbcTypeName + "'.");
						if (typeMappings.containsKey(jdbcTypeName)) {
							typeName = jdbcTypeName;
						} else {
							throw new RuntimeException("No type mapping found for db type '" + dbTypeName + "' or jdbc type '" + jdbcTypeName + "'.");
						}
					}
					typeClass = this.getOrCreateDataType(typeName);

					int required = !this.databaseHelper.isColumnNullable(tableName,columnName) == true ? 1 : 0;
					
					Integer precision = new Integer(((BigDecimal)columnRs.getObject("COLUMN_SIZE")).intValue());
					Integer scale = ((BigDecimal)columnRs.getObject("DECIMAL_DIGITS")) != null ? new Integer(((BigDecimal)columnRs.getObject("DECIMAL_DIGITS")).intValue()) : null;
					String defaultValue = null;//columnRs.getString("COLUMN_DEF");
					String description = columnRs.getString("REMARKS");
					String databaseType = columnRs.getString("TYPE_NAME");
					Integer jdbcType = new Integer(((BigDecimal)columnRs.getObject("DATA_TYPE")).intValue());
					
					createAttribute(umlClass, primaryKeyColumns, columnName, attributeName, typeClass, required, precision, scale, defaultValue, description, databaseType, jdbcType);

				}
			} else {
				logger.warn("Column '" + columnName + "' does not match columnNamePattern.");
			}
		}
		DbUtils.closeQuietly(columnRs);
	}

	private void createAttribute(
			org.eclipse.uml2.uml.Class umlClass,
			final Collection<String> primaryKeyColumns,
			final String columnName, 
			final String attributeName,
			org.eclipse.uml2.uml.Class typeClass, 
			int required,
			Integer precision,
			Integer scale,
			String defaultValue,
			String description,
			String databaseType,
			Integer jdbcType) {
		
		Property attribute = umlClass.createOwnedAttribute(attributeName, typeClass, required, 1);
		
		
		attribute.applyStereotype(persistentProperty);
		
		attribute.setValue(persistentProperty, "column", columnName);
		attribute.setValue(persistentProperty, "primaryKey", primaryKeyColumns.contains(columnName) ? true : false);
		attribute.setValue(persistentProperty, "description", description);
		attribute.setValue(persistentProperty, "precision", precision);
		attribute.setValue(persistentProperty, "scale", scale);
		attribute.setValue(persistentProperty, "defaultValue", defaultValue);
		attribute.setValue(persistentProperty, "databaseType", databaseType);
		attribute.setValue(persistentProperty, "jdbcType", jdbcType);
		// TODO autoIncrement
		
	}

	protected org.eclipse.uml2.uml.Class getOrCreateDataType(String type) {
		type = (String) this.typeMappings.get(type);

		// TODO load types from profile
		// TODO only types in root package allowed yet - change this to fqn

		org.eclipse.uml2.uml.Class datatype = null;

		if (model.getMember(type) == null) {
			datatype = model.createOwnedClass(type, false);
		} else if (datatype == null) {
			datatype = (org.eclipse.uml2.uml.Class) model.getOwnedMember(type);
		}

		return (org.eclipse.uml2.uml.Class) datatype;
	}

	protected void createAssociations(String tableName) throws SQLException {
		Collection<String> primaryKeys = this.databaseHelper.getPrimaryKeys(tableName);
		ResultSet columnRs = this.databaseHelper.getImportedKeys(tableName);
		while (columnRs.next()) {
			
			String fkColumnName = columnRs.getString("FKCOLUMN_NAME");
			this.addForeignKey(tableName, fkColumnName);
			
			//String pkColumnName = columnRs.getString("PKCOLUMN_NAME");
			
			String foreignTableName = columnRs.getString("PKTABLE_NAME");

			int primaryUpper = -1;
			if (primaryKeys.contains(fkColumnName)) {
				primaryUpper = 1;
			}

			String primaryEndName = null;

			org.eclipse.uml2.uml.Class primaryParticipant = (org.eclipse.uml2.uml.Class) this.classes.get(tableName);

			boolean required = !this.databaseHelper.isColumnNullable(tableName, fkColumnName);

			int foreignLower = 0;
			if (required) {
				foreignLower = 1;
			}

			int deleteRule = columnRs.getInt("DELETE_RULE");
			AggregationKind foreignAggregation = AggregationKind.NONE_LITERAL;
			if (deleteRule == DatabaseMetaData.importedKeyCascade) {
				foreignAggregation = AggregationKind.COMPOSITE_LITERAL;
			}

			String foreignEndName = StringHelper.foreignKeyAttributeName(fkColumnName);

			final org.eclipse.uml2.uml.Class foreignParticipant = (org.eclipse.uml2.uml.Class) this.classes.get(foreignTableName);
			
			if (foreignParticipant == null) {
				throw new RuntimeException("The table '" + foreignTableName + "' is not available.");
			}

			createAssociation(primaryUpper, primaryEndName, primaryParticipant, 
					foreignLower, foreignAggregation, foreignEndName,foreignParticipant, fkColumnName);

		}
		DbUtils.closeQuietly(columnRs);
	}

	private void createAssociation(
			int primaryUpper, 
			String primaryEndName,
			org.eclipse.uml2.uml.Class primaryParticipant, 
			int foreignLower,
			AggregationKind foreignAggregation, 
			String foreignEndName,
			final org.eclipse.uml2.uml.Class foreignParticipant,
			String fkColumnName) {
		
		logger.info("Adding association between '" + primaryParticipant.getName() + "' and '" + foreignParticipant.getName() + "'.");
		
		Association umlAssociation = primaryParticipant.createAssociation(
				true, AggregationKind.NONE_LITERAL, primaryEndName, 0,
				primaryUpper, foreignParticipant, true, foreignAggregation,
				foreignEndName, foreignLower, 1);

		umlAssociation.applyStereotype(this.persistentAssociation);
		
		umlAssociation.setValue(this.persistentAssociation, "column", fkColumnName);
		
	}

	protected void addForeignKey(String tableName, String columnName) {
		if (StringUtils.isNotBlank(tableName) && StringUtils.isNotBlank(columnName)) {
			Collection<String> foreignKeys = this.foreignKeys.get(tableName);
			if (foreignKeys == null) {
				foreignKeys = new HashSet<String>();
			}
			foreignKeys.add(columnName);
			this.foreignKeys.put(tableName, foreignKeys);
		}
	}

	protected boolean hasForeignKey(String tableName, String columnName) {
		boolean hasForeignKey = false;
		if (StringUtils.isNotBlank(tableName) && StringUtils.isNotBlank(columnName)) {
			Collection<String> foreignKeys = this.foreignKeys.get(tableName);
			if (foreignKeys != null) {
				hasForeignKey = foreignKeys.contains(columnName);
			}
		}
		return hasForeignKey;
	}

}
